<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace API;
class DatabaseAdmin extends \App\SessionObject {
    /**
     * listDatabaseObjects(request, response) - lists the databases
     * Returns all of the visible databases.
     * The db_name specifies the current database. If none is given, the first database in the list is assumed to be the current one.
     * Also returns all of the tables in the current database.
     * The table_name specifies the current table. If none is given, the first table in the list is assumed to be the current one.
     * Also returns all of the columns in the current table.
     * @param string  db_name    [IN]  - (optional) the current database name
     * @param string  table_name [IN]  - (optional) the table name
     * @param boolean success    [OUT] - whether the call succeeded
     * @param string  db_name    [OUT] - current db_name (same as INPUT db_name unless it is not provided or that name doesn't exist. then it is the first db_name in the list. else null.)
     * @param integer db_id      [OUT] - current db_id (corresponding to INPUT db_name unless it is not provided or that name doesn't exist. then it is from the first database in the list. else null.)
     * @param array   databases  [OUT] - an array of associative arrays, each representing a visible database
     * @param string  table_name [OUT] - current table_name (same as INPUT table_name unless it is not provided or that name doesn't exist. then it is the first table_name in the database. else null.)
     * @param integer table_id   [OUT] - current table_id (corresponding to INPUT table_name unless it is not provided or that name doesn't exist. then it is from the first table in the database. else null.)
     * @param array   tables     [OUT] - an array of associative arrays, each representing a visible table
     * @param array   columns    [OUT] - an array of column names of the current table
     */
    public function listDatabaseObjects ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();

        $db_name    = isset($request['db_name'])    ? $request['db_name']    : null;
        $table_name = isset($request['table_name']) ? $request['table_name'] : null;

        $authdb = $context->authdb();
        $response = array('success' => true);

        # get all databases we are authorized to see
        $databases = $context->get_database_defs();
        $count     = count($databases);

        $db_id     = null;
        $schema_id = null;
        # if a specific database was identified as being current...
        if (isset($db_name)) {
            # look for it in the list of authorized databases
            for ($i = 0; $i < $count; $i++) {
                if ($db_name === $databases[$i]['db_name']) {
                    $db_id     = $databases[$i]['db_id'];
                    $response['db_name'] = $db_name;
                    $response['db_id']   = $db_id;
                    break;
                }
            }
            # if we didn't find it, unset the current name
            if (!isset($db_id)) {
                $db_name = null;
            }
        }
        # if we still don't know an identified, authorized database...
        if (!isset($db_name)) {
            # choose the first database in the list
            if ($count > 0) {
                $db_id   = $databases[0]['db_id'];
                $db_name = $databases[0]['db_name'];
                $response['db_name'] = $db_name;
                $response['db_id']   = $db_id;
            }
            # or admit there are none
            else {
                $response['db_name'] = null;
                $response['db_id']   = null;
                $db_id   = null;
                $db_name = null;
            }
        }
        $response['databases'] = array();
        for ($i = 0; $i < $count; $i++) {
            $response['databases'][] = array(
                'db_id' => $databases[$i]['db_id'],
                'db_name' => $databases[$i]['db_name'],
                'db_label' => $databases[$i]['db_label']
            );
        }
       
        if (isset($db_id)) {
            $db = $context->database($db_name);

            # TODO: remove this when we get the automated metadata stuff working right
            $schema_names = $db->get_schema_names();   # initialize metadata if necessary
            if (count($schema_names) == 0) $db->init_table_defs();

            # get all tables we are authorized to see for the selected database
            $table_names              = $db->get_table_names();   # initialize the metadata if necessary
            $table_defs_by_table_name = $db->get_table_defs();
            $table_defs = array();
            foreach ($table_defs_by_table_name as $table_def) {
                $table_defs[] = array(
                    'table_id' => $table_def['table_id'],
                    'table_name' => $table_def['table_name'],
                    'table_label' => $table_def['table_label']
                );
            }

            $count = count($table_defs);
            $table_id = null;
            # if a specific table was identified as being current...
            if (isset($table_name)) {
                # look for it in the list of authorized tables
                for ($i = 0; $i < $count; $i++) {
                    if ($table_name === $table_defs[$i]['table_name']) {
                        $table_id     = $table_defs[$i]['table_id'];
                        $response['table_name'] = $table_name;
                        $response['table_id']   = $table_id;
                        break;
                    }
                }
                # if we didn't find it, unset the current name
                if (!isset($table_id)) {
                    $table_name = null;
                }
            }
            # if we still don't know an identified, authorized table...
            if (!isset($table_name)) {
                # choose the first table in the list
                if ($count > 0) {
                    $table_id   = $table_defs[0]['table_id'];
                    $table_name = $table_defs[0]['table_name'];
                    $response['table_name'] = $table_name;
                    $response['table_id']   = $table_id;
                }
                # or admit there are none
                else {
                    $response['table_name'] = null;
                    $response['table_id']   = null;
                }
            }
            $response['tables'] = $table_defs;

            if (isset($table_id)) {
                $table_def = $db->get_table_def($table_name);
                # get all columns we are authorized to see for the selected table
                $response['columns'] = $table_def['columns'];
            }
            else {
                $response['columns'] = array();
            }
        }
        else {
            $response['tables'] = array();
            $response['table_name'] = null;
            $response['table_id']   = null;
        }

        if ($trace) trace_exit();
    }

    /**
     * createDatabase(request, response) - creates a new database
     * Requests the creation of a new database.
     * This is not necessarily immediate (synchronous). It may take some time to provision the database.
     * Therefore, the call may be successful, but the result may indicate that the database is not yet ready for use.
     * @param string  db_name            [IN] - (required) the name of the database
     * @param integer db_owner_group_id  [IN] - (optional) the group_id that owns the database. if not equal to owner_group_id, the database must exist and be readable by others
     * @param integer db_owner_db_name   [IN] - (optional) the db_name of the existing database owned by db_owner_group_id that you are trying to link to (instead of make a new database)
     * @param string  db_label           [IN] - (optional) a label to be displayed that describes the database (default is the db_name with spaces and initial capitals)
     * @param string  dbtype             [IN] - (default mysql) [sqlite, mysql, oracle]
     * @param integer user_mode          [IN] - (optional) a bitmask. 4=read, 2=write, 1=modify-ddl.
     * @param integer group_mode         [IN] - (optional) a bitmask. 4=read, 2=write, 1=modify-ddl.
     * @param integer other_mode         [IN] - (optional) a bitmask. 4=read, 2=write, 1=modify-ddl.
     * @param array   db_desc            [IN] - (optional) a full sentence or more describing this database
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     * @param integer allocated          [OUT]- true=database allocated. false=not yet allocated
     * Global Runtime Data (Developer using the API cannot control this.) (This applies to every one of the database calls, so it is not repeated.)
     * @cookie integer group_id          [IN] - (default 1) the group_id of the currently selected group
     * @cookie string  db_env            [IN] - (default 'prod') the current database environment (e.g. 'prod')
     */
    public function createDatabase ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();

        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) {
            throw new \Exception('Cannot create database without a db_name');
        }
        $owner_group_id    = $context->getAuthenticatedGroupId();
        $db_owner_group_id = isset($request['db_owner_group_id']) ? $request['db_owner_group_id'] : $owner_group_id;
        $authdb            = $context->authdb();
        $db_label          = isset($request['db_label'])          ? $request['db_label']          : $authdb->make_label($db_name);
        $dbtype            = isset($request['dbtype'])            ? $request['dbtype']            : 'mysql';
        $user_mode         = isset($request['user_mode'])         ? $request['user_mode']         : 7;
        $group_mode        = isset($request['group_mode'])        ? $request['group_mode']        : 7;
        $other_mode        = isset($request['other_mode'])        ? $request['other_mode']        : 0;
        $db_desc           = isset($request['db_desc'])           ? $request['db_desc']           : $db_label;

        $response = array('success' => true);
        $admin = $context->sessionObject('administration', array('class' => 'App\\Administration'));
        $allocated = $admin->requestNewDatabase($db_name, $owner_group_id, $db_owner_group_id, $db_label, $dbtype, $user_mode, $group_mode, $other_mode, $db_desc);
        $response['allocated'] = $allocated;

        if ($trace) trace_exit($response);
    }

    /**
     * dropDatabase(request, response) - drops (deletes) a database
     * Requests the creation of a new database.
     * This is not necessarily immediate (synchronous). It may take some time to provision the database.
     * Therefore, the call may be successful, but the result may indicate that the database is not yet fully deallocated.
     * In that case, the db_name cannot be reused until it is successfully deallocated by a different server-side process.
     * @param string  db_name            [IN] - the name of the database
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     * @param integer deallocated        [OUT]- true=database deallocated. false=not yet deallocated
     */
    public function dropDatabase ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();

        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) {
            throw new \Exception('Cannot drop database without a db_name');
        }

        $reserved_db_names = array('auth','log','meta','group_private','user_private','main','app');
        if (in_array($db_name, $reserved_db_names)) {
            throw new \Exception("The '$db_name' database cannot be dropped because it is a reserved database");
        }

        $owner_group_id    = $context->getAuthenticatedGroupId();

        $response = array('success' => true);
        $admin = $context->sessionObject('administration', array('class' => 'App\\Administration'));
        $deallocated = $admin->requestDropDatabase($db_name, $owner_group_id);
        $response['deallocated'] = $deallocated;

        if ($trace) trace_exit();
    }

    /**
     * listDatabases(request, response) - List the databases visible to a user in the currently selected group.
     * GET /api/service/databaseAdmin/listDatabases
     * This API is suitable to use as an end-point for an ExtJS grid.
     * @param  boolean success           [OUT]- whether the call was successful or not
     * @param  string  message           [OUT]- error message in case the call was not successful
     * @param  integer total             [OUT]- the total number of rows (estimated) that would be returned if start=1 and limit=0
     * @param  array   data              [OUT]- an array of associative arrays with rows of data
     *                                          "db_id": "1",
     *                                          "db_name": "auth",
     *                                          "db_label": "Global Authentication Database",
     *                                          "db_desc": null,
     *                                          "dbtype": "mysql",
     *                                          "status": "A",
     *                                          "reserved_size": null,
     *                                          "est_current_size": null
     */
    public function listDatabases ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $response = array('success' => true);
        $authdb    = $context->authdb();
        $db_env    = $context->db_env();
        $group_id  = $context->getAuthenticatedGroupId();
        #$db_select = 'select db.db_id, db.db_name, db.db_label, db.db_desc, db.db_connection_id, dbc.db_instance_id, ' .
        $db_select = 'select db.db_id, db.db_name, db.db_label, db.db_desc, ' .
                     'dbc.dbtype, db.status, db.reserved_size, db.est_current_size ' .
                     'from db inner join db_connection dbc on dbc.db_connection_id = db.db_connection_id';
        $response['data']  = $authdb->get_hashes("$db_select where db.db_env = ? and db.owner_group_id = ? union $db_select where db.other_mode > 0 order by db_id", array($db_env, $group_id));
        $response['total'] = count($response['data']);
        if ($trace) trace_exit();
    }

    /**
     * getDatabaseInfo(request, response) - tbd
     * GET /api/service/databaseAdmin/getDatabaseInfo
     * @param string  db_name            [IN] - the name of the database
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     * @param object  data               [OUT]- an associative arrays of attributes about the database
     *                                          "db_id": "1",
     *                                          "db_name": "auth",
     *                                          "db_label": "Global Authentication Database",
     *                                          "db_desc": null,
     *                                          "dbtype": "mysql",
     *                                          "status": "A",
     *                                          "reserved_size": null,
     *                                          "est_current_size": null
     */
    public function getDatabaseInfo ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot get database info without a db_name');
        $response = array('success' => true);
        $authdb    = $context->authdb();
        $db_env    = $context->db_env();
        $group_id  = $context->getAuthenticatedGroupId();
        #$db_select = 'select db.db_id, db.db_name, db.db_label, db.db_desc, db.db_connection_id, dbc.db_instance_id, ' .
        $db_select = 'select db.db_id, db.db_name, db.db_label, db.db_desc, ' .
                     'dbc.dbtype, db.status, db.reserved_size, db.est_current_size ' .
                     'from db inner join db_connection dbc on dbc.db_connection_id = db.db_connection_id';
        $response['data']  = $authdb->get_hash("$db_select where db.db_env = ? and db.owner_group_id = ? and db.db_name = ? union $db_select where db.other_mode > 0 and db.db_name = ? order by db_id", array($db_env, $group_id, $db_name, $db_name));
        if ($trace) trace_exit();
    }

    /**
     * setDatabaseInfo(request, response) - tbd
     * PUT /api/service/databaseAdmin/setDatabaseInfo
     * @param string  db_name            [IN] - the name of the database
     * @param object  data               [IN] - an associative arrays of attributes about the database that should be updated
     *                                          "dbtype",
     *                                          "reserved_size",
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function setDatabaseInfo ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot set database info without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * createTable(request, response) - tbd
     * PUT /api/service/databaseAdmin/createTable
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function createTable ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : '';
        if (!$db_name) throw new \Exception('Cannot create table without a db_name');
        $db = $context->database($db_name);
        $table_name        = isset($request['table_name'])        ? $request['table_name']        : '';
        if (!$table_name) throw new \Exception('Cannot create table without a table_name');
        try {
            $table_def = $db->get_table_def($table_name);
            throw new \Exception("Table $db_name.$table_name is already defined");
        }
        catch (\Exception $e) {
            # do nothing
        }
        $db->create_table($request);
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    public function defineTable ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : '';
        if (!$db_name) throw new \Exception('Cannot create table without a db_name');
        $db = $context->database($db_name);
        $table_name        = isset($request['table_name'])        ? $request['table_name']        : '';
        if (!$table_name) throw new \Exception('Cannot create table without a table_name');
        $table_exists = 0;
        try {
            $table_def = $db->get_table_def($table_name);
            $table_exists = 1;
        }
        catch (\Exception $e) {
            # do nothing
        }
        $schema_name = $db->get_schema_name();
        $table_def_from_db = $db->get_table_def_from_db($table_name, $schema_name);
        if (isset($table_def_from_db)) {
            #if (isset($request['new_table']) && $request['new_table']) throw new \Exception("Table $table_name already exists");
            $db->alter_table($request, $table_def_from_db);
        }
        else {
            #if (isset($request['new_table']) && !$request['new_table']) throw new \Exception("Table $table_name does not exist");
            $db->create_table($request);
        }
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * listTables(request, response) - List the databases visible to a user in the currently selected group.
     * GET /api/service/databaseAdmin/listTables
     * This API is suitable to use as an end-point for an ExtJS grid.
     * @param  boolean success           [OUT]- whether the call was successful or not
     * @param  string  message           [OUT]- error message in case the call was not successful
     * @param  integer total             [OUT]- the total number of rows (estimated) that would be returned if start=1 and limit=0
     * @param  array   data              [OUT]- an array of associative arrays with rows of data
     *                                          "table_name": "auth_email",
     *                                          "table_label": "Auth Email",
     *                                          "physical_ind": "Y",
     */
    public function listTables ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name']) ? $request['db_name'] : null;
        if (!isset($db_name)) throw new \Exception('db_name not supplied');
        $response = array('success' => true);
        $db       = $context->database($db_name);
        $table_defs = $db->get_table_defs();
        foreach ($table_defs as $table_def) {
            $data[] = array(
                'table_name' => $table_def['table_name'],
                'table_label' => $table_def['table_label'],
                'physical_ind' => $table_def['physical_ind']
            );
        }
        $response['data']  = $data;
        $response['total'] = count($data);
        if ($trace) trace_exit();
    }

    /**
     * getTableInfo(request, response) - tbd
     * GET /api/service/databaseAdmin/getTableInfo
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function getTableInfo ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot get table info without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * renameTable(request, response) - tbd
     * PUT /api/service/databaseAdmin/renameTable
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  new_table_name     [IN] - the new name of the table
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function renameTable ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot rename table without a db_name');
        $db = $context->database($db_name);
        $table_name        = isset($request['table_name'])        ? $request['table_name']        : '';
        if (!$table_name) throw new \Exception('Cannot rename table without a table_name');
        $new_table_name    = isset($request['new_table_name'])    ? $request['new_table_name']    : '';
        if (!$new_table_name) throw new \Exception('Cannot rename table without a new_table_name');
        $table_def = $db->get_table_def($table_name);  # throw an exception if it does not exist
        try {
            $new_table_def = $db->get_table_def($new_table_name);
            throw new \Exception("Table $db_name.$table_name is already defined");
        }
        catch (\Exception $e) {
            # do nothing. the table is not supposed to exist.
        }
        $new_table_label = isset($request['new_table_label']) ? $request['new_table_label'] : null;
        $db->rename_table($table_name, $new_table_name, $new_table_label);
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * alterTable(request, response) - tbd
     * PUT /api/service/databaseAdmin/alterTable
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - (optional) either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param string  drop_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     * @param string  add_columns        [IN] - (optional) either a (csv) list of column_names or an array of column_names
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   add_unique_indexes [IN] - (optional) an array of arrays of column_names which make up NEW alternate keys TO ADD
     * @param array   drop_unique_indexes[IN] - (optional) an array of arrays of column_names which make up EXISTING alternate keys TO DROP
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     * @param array   add_indexes        [IN] - (optional) an array of arrays of column_names which make up NEW non-unique indexes TO ADD
     * @param array   drop_indexes       [IN] - (optional) an array of arrays of column_names which make up EXISTING non-unique indexes TO ADD
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function alterTable ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name = isset($request['db_name']) ? $request['db_name'] : null;
        if (!isset($db_name)) throw new \Exception('Cannot alter table without a db_name');
        $db = $context->database($db_name);
        $table_def = $db->get_table_def($table_name);   # will throw an exception if  it doesn't exist
        $db->alter_table($request);
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * dropTable(request, response) - tbd
     * DELETE /api/service/databaseAdmin/dropTable
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function dropTable ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot drop table without a db_name');
        $db = $context->database($db_name);
        $table_name        = isset($request['table_name'])        ? $request['table_name']        : null;
        if (!isset($table_name)) throw new \Exception('Cannot drop table without a table_name');
        $table_def = $db->get_table_def($table_name);  # throw an exception if it does not exist
        $db->drop_table($table_name);
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

# SEE THE FILE API FOR THIS FUNCTION
#    /**
#     * exportTable(request, response) - tbd
#     * POST /api/service/databaseAdmin/exportTable
#     * @param string  db_name            [IN] - the name of the database
#     * @param string  table_name         [IN] - the name of the table
#     * @param string  view               [IN] - (optional) the name of the view ({db_name}.{table_name}.{view_code})
#     * @param boolean success            [OUT]- whether the call was successful or not
#     * @param string  message            [OUT]- error message in case the call was not successful
#     */
#    public function exportTable ($request, &$response) {
#        global $trace, $context;
#        if ($trace) trace_entry();
#        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
#        if (!isset($db_name)) throw new \Exception('Cannot export table without a db_name');
#        $response = array('success' => true);
#        if ($trace) trace_exit();
#    }

# SEE THE FILE API FOR THIS FUNCTION
#    /**
#     * importTable(request, response) - tbd
#     * POST /api/service/databaseAdmin/importTable
#     * @param string  db_name            [IN] - the name of the database
#     * @param string  table_name         [IN] - the name of the table
#     * @param string  view               [IN] - (optional) the name of the view ({db_name}.{table_name}.{view_code})
#     * @param boolean success            [OUT]- whether the call was successful or not
#     * @param string  message            [OUT]- error message in case the call was not successful
#     */
#    public function importTable ($request, &$response) {
#        global $trace, $context;
#        if ($trace) trace_entry();
#        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
#        if (!isset($db_name)) throw new \Exception('Cannot import table without a db_name');
#        $response = array('success' => true);
#        if ($trace) trace_exit();
#    }

    /**
     * refreshTableMetadata(request, response) - update the table metadata based on the physical table and additional data
     * GET /api/service/databaseAdmin/refreshTableMetadata
     * This API is not intended to be called from the client. It is more for testing, support, cleanup, and administration.
     * @param  string  db_name           [IN] - the database name
     * @param  string  table_name        [IN] - the table name
     * @param  boolean success           [OUT]- whether the call was successful or not
     * @param  string  message           [OUT]- error message in case the call was not successful
     */
    public function refreshTableMetadata ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name    = isset($request['db_name'])    ? $request['db_name']    : null;
        $table_name = isset($request['table_name']) ? $request['table_name'] : null;
        if (!isset($db_name))    throw new \Exception('No db_name provided');
        if (!isset($table_name)) throw new \Exception('No table_name provided');
        $response    = array('success' => true);
        $db          = $context->database($db_name);
        #public function refresh_table_metadata ($schema, $table, $table_def_from_db = null) {}
        $db->refresh_table_metadata($db->get_schema_name(), $table_name);
        if ($trace) trace_exit();
    }

    /**
     * listColumns(request, response) - List the databases visible to a user in the currently selected group.
     * GET /api/service/databaseAdmin/listColumns
     * This API is suitable to use as an end-point for an ExtJS grid.
     * @param  string  db_name           [IN] - the database name
     * @param  string  table_name        [IN] - the table name
     * @param  boolean success           [OUT]- whether the call was successful or not
     * @param  string  message           [OUT]- error message in case the call was not successful
     * @param  integer total             [OUT]- the total number of rows
     * @param  array   data              [OUT]- an array of associative arrays with rows of data
     *                                          "column_name": "user_id",
     *                                          "column_alias": "ui",
     *                                          "column_label": "User Id",
     *                                          "column_type": "integer",
     *                                          "max_length": "10",
     *                                          "not_null_ind": "Y",
     *                                          "default_value": null,
     *                                          "physical_ind": "Y",
     *                                          "primary_key_ind": "Y",
     *                                          "auto_increment_ind": "N",
     */
    public function listColumns ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name    = isset($request['db_name'])    ? $request['db_name']    : null;
        $table_name = isset($request['table_name']) ? $request['table_name'] : null;
        if (!isset($db_name))    throw new \Exception('No db_name provided');
        if (!isset($table_name)) throw new \Exception('No table_name provided');
        $response    = array('success' => true);
        $db          = $context->database($db_name);
        $table_def   = $db->get_table_def($table_name);
        $columns     = $table_def['columns'];
        $column_defs = $table_def['column'];
        $data = array();
        foreach ($columns as $column) {
            $column_def = $column_defs[$column];
            $data[] = array(
                'column_name' => $column_def['column_name'],
                'column_label' => $column_def['column_label'],
                'column_type' => $column_def['column_type'],
                'max_length' => $column_def['max_length'],
                'not_null_ind' => $column_def['not_null_ind'],
                'default_value' => $column_def['default_value'],
                'physical_ind' => $column_def['physical_ind'],
                'primary_key_ind' => $column_def['primary_key_ind'],
                'auto_increment_ind' => $column_def['auto_increment_ind']
            );
        }
        $response['data']  = $data;
        $response['total'] = count($data);
        if ($trace) trace_exit();
    }

    /**
     * defineDisplayColumn(request, response) - tbd
     * PUT /api/service/databaseAdmin/defineDisplayColumn
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param object  data               [IN] - attributes of the column to be defined
     *                                        * column_name
     *                                        * column_alias
     *                                        * column_label
     *                                        * column_type
     *                                        * order_idx
     *                                        * max_length
     *                                        * physical_ind
     *                                        * dbexpr
     *                                        * agg_dbexpr
     *                                        * expr
     *                                        * display_format
     *                                        * display_justify
     *                                        * display_width
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function defineDisplayColumn ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot define display column without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * listRelationships(request, response) - List the databases visible to a user in the currently selected group.
     * GET /api/service/databaseAdmin/listRelationships
     * This API is suitable to use as an end-point for an ExtJS grid.
     * @param  string  db_name           [IN] - the database name
     * @param  string  table_name        [IN] - the table name
     * @param  boolean success           [OUT]- whether the call was successful or not
     * @param  string  message           [OUT]- error message in case the call was not successful
     * @param  integer total             [OUT]- the total number of rows
     * @param  array   data              [OUT]- an array of associative arrays with rows of data
     *                                          "relationship_alias": "tu",
     *                                          "relationship_label": "tracked_url",
     *                                          "order_idx": "9999",
     *                                          "to_table": "tracked_url",
     *                                          "join_on_clause": "tu.url_id = tul.url_id",
     *                                          "dependencies": null,
     *                                          "to_zero_ind": "N",
     *                                          "to_many_ind": "N"
     */
    public function listRelationships ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name    = isset($request['db_name'])    ? $request['db_name']    : null;
        $table_name = isset($request['table_name']) ? $request['table_name'] : null;
        if (!isset($db_name))    throw new \Exception('No db_name provided');
        if (!isset($table_name)) throw new \Exception('No table_name provided');
        $response          = array('success' => true);
        $db                = $context->database($db_name);
        $table_def         = $db->get_table_def($table_name);
        $relationships     = $table_def['relationships'];
        $relationship_defs = $table_def['relationship'];
        $data = array();
        foreach ($relationships as $relationship) {
            $relationship_def = $relationship_defs[$relationship];
            $data[] = array(
                'relationship_alias' => $relationship_def['relationship_alias'],
                'relationship_label' => $relationship_def['relationship_label'],
                'order_idx' => $relationship_def['order_idx'],
                'to_table' => $relationship_def['to_table'],
                'join_on_clause' => $relationship_def['join_on_clause'],
                'dependencies' => $relationship_def['dependencies'],
                'to_zero_ind' => $relationship_def['to_zero_ind'],
                'to_many_ind' => $relationship_def['to_many_ind']
            );
        }
        $response['data']  = $data;
        $response['total'] = count($data);
        if ($trace) trace_exit();
    }

    /**
     * defineRelatedTable(request, response) - tbd
     * PUT /api/service/databaseAdmin/defineRelatedTable
     * @param string  table_name         [IN] - the name of the table
     * @param string  relationship_alias [IN] - a short abbreviation for the relationship that is unique for each table
     * @param string  relationship_label [IN] - the text to be displayed for the relationship
     * @param string  order_idx          [IN] - an ordering number the determines where in the list of relationships this relationship will appear
     * @param string  to_table           [IN] - the table that this table is related to
     * @param string  join_on_clause     [IN] - the "join on" clause (e.g. between the brackets in "left join geo_country gc on [gc.country_cd = arp.country_cd]")
     * @param string  dependencies       [IN] - a csv list of the other relationship_aliases which must be active within a query in order to use this relationship
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function defineRelatedTable ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot define related tables without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * copyView(request, response) - tbd
     * PUT /api/service/databaseAdmin/copyView
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  view               [IN] - the name of the view ({db_name}.{table_name}.{view_code})
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function copyView ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot copy view without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * listViews(request, response) - tbd
     * GET /api/service/databaseAdmin/listViews
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  view               [IN] - the name of the view ({db_name}.{table_name}.{view_code})
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function listViews ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot list views without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * getViewInfo(request, response) - tbd
     * GET /api/service/databaseAdmin/getViewInfo
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  view               [IN] - the name of the view ({db_name}.{table_name}.{view_code})
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function getViewInfo ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name           = isset($request['db_name'])           ? $request['db_name']           : null;
        if (!isset($db_name)) throw new \Exception('Cannot get view info without a db_name');
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * renameView(request, response) - tbd
     * PUT /api/service/databaseAdmin/renameView
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  view               [IN] - the name of the view ({db_name}.{table_name}.{view_code})
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function renameView ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name        = isset($request['db_name'])       ? $request['db_name']       : null;
        $table_name     = isset($request['table_name'])    ? $request['table_name']    : null;
        $view           = isset($request['view'])          ? $request['view']          : null;
        $new_view_name  = isset($request['new_view_name']) ? $request['new_view_name'] : null;
        $db = $context->database($db_name);
        $db_owner_group_id = $db->db_owner_group_id;
        $owner_group_id = $context->getAuthenticatedGroupId();
        if (!isset($db_name)) throw new \Exception('Cannot drop view without a db_name');
        $metadb = $context->metadb();
        $metadb->begin_work();
        try {
            $metadb->execute("update {meta_schema_}db_view set view_label = ? where db_owner_group_id = ? and view_key = ?",
                       array($new_view_name, $db_owner_group_id, $view));
            $metadb->execute("update {meta_schema_}app_tree set tree_label = ? where owner_group_id = ? and action = ?",
                       array($new_view_name, $owner_group_id, $view));
            $metadb->commit();
        }
        catch (\Exception $e) {
            $metadb->rollback();
            throw new \Exception($e->getMessage());
        }
        $response = array('success' => true);
        if ($trace) trace_exit();
    }

    /**
     * dropView(request, response) - tbd
     * DELETE /api/service/databaseAdmin/dropView
     * @param string  db_name            [IN] - the name of the database
     * @param string  table_name         [IN] - the name of the table
     * @param string  view               [IN] - the name of the view ({db_name}.{table_name}.{view_code})
     * @param boolean success            [OUT]- whether the call was successful or not
     * @param string  message            [OUT]- error message in case the call was not successful
     */
    public function dropView ($request, &$response) {
        global $trace, $context;
        if ($trace) trace_entry();
        $db_name        = isset($request['db_name'])    ? $request['db_name']    : null;
        $table_name     = isset($request['table_name']) ? $request['table_name'] : null;
        $view           = isset($request['view'])       ? $request['view']       : null;
        if (!isset($db_name)) throw new \Exception('Cannot drop view without a db_name');
        $db = $context->database($db_name);
        $db_owner_group_id = $db->db_owner_group_id;
        $owner_group_id = $context->getAuthenticatedGroupId();
        $metadb = $context->metadb();
        $metadb->begin_work();
        try {
            $metadb->execute("delete from {meta_schema_}db_view where db_owner_group_id = ? and view_key = ?", array($db_owner_group_id, $view));
            $metadb->execute("delete from {meta_schema_}app_tree where owner_group_id = ? and action = ?", array($owner_group_id, $view));
            $metadb->commit();
        }
        catch (\Exception $e) {
            $metadb->rollback();
            throw new \Exception($e->getMessage());
        }
        $response = array('success' => true);
        if ($trace) trace_exit();
    }
}

#-- -----------------------------------------------------
#-- Table db_instance (GLOBAL and GROUP)
#-- -----------------------------------------------------
#create table if not exists db_instance (
#  db_instance_id           INTEGER          NOT NULL constraint db_instance_pk primary key autoincrement,
#  db_instance_name         VARCHAR(255)     NOT NULL,                            -- dbtype:dbhost. if this is not unique, use the primary dsn
#  db_owner_group_id        INTEGER          NOT NULL,                            -- this describes which group actually owns the instance
#  order_idx                INTEGER          NOT NULL DEFAULT 9999,
#  dbtype                   VARCHAR(99)      NOT NULL,                            -- [sqlite, mysql, oracle]
#  dbhost                   VARCHAR(99)      NULL,
#  user_mode                TINYINT          not null default 7,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  group_mode               TINYINT          not null default 7,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  other_mode               TINYINT          not null default 0,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  owner_group_id           INTEGER          NOT NULL DEFAULT 1,                  -- 1 is the 'all-users' group
#  modify_user_id           INTEGER          NOT NULL DEFAULT 0,
#  create_user_id           INTEGER          NOT NULL DEFAULT 0,
#  modify_dttm              DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
#  create_dttm              DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
#);
#-- -----------------------------------------------------
#-- Table db_connection (GLOBAL and GROUP)
#-- -----------------------------------------------------
#create table if not exists db_connection (
#  db_connection_id         INTEGER          NOT NULL constraint db_connection_pk primary key autoincrement,
#  db_connection_name       VARCHAR(255)     NOT NULL,                            -- dbtype:dbhost:default_schema:dbuser. If this is not unique, use the dsn
#  db_instance_id           INTEGER          NOT NULL,                            -- ID of the db_instance this db_connection is to
#  db_owner_group_id        INTEGER          NOT NULL,                            -- this describes which group actually owns the instance
#  dbtype                   VARCHAR(99)      NOT NULL,                            -- [sqlite, mysql, oracle]
#  dbhost                   VARCHAR(99)      NULL,                                -- can be a shortened, memorable version of the full hostname (non-qualified?)
#  default_schema           VARCHAR(99)      NULL,                                -- which schema to connect to by default (if not set, all SQL uses fully-qualified table names)
#  db_class                 VARCHAR(99)      NOT NULL,                            -- what PHP class is used to connect to the database
#  dsn                      VARCHAR(255)     NULL,                                -- dsn = "data source name", the unique string that the db_class can use to connect to the database
#  dbuser                   VARCHAR(99)      NULL,
#  dbpass                   VARCHAR(99)      NULL,
#  dboptions                VARCHAR(255)     NULL,                                -- additional connection options
#  order_idx                INTEGER          NOT NULL DEFAULT 9999,
#  user_mode                TINYINT          NOT null default 7,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  group_mode               TINYINT          NOT null default 7,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  other_mode               TINYINT          NOT null default 0,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  owner_group_id           INTEGER          NOT NULL DEFAULT 1,                  -- 1 is the 'all-users' group
#  modify_user_id           INTEGER          NOT NULL DEFAULT 0,
#  create_user_id           INTEGER          NOT NULL DEFAULT 0,
#  modify_dttm              DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
#  create_dttm              DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
#);
#-- -----------------------------------------------------
#-- Table db (GLOBAL and GROUP)
#-- -----------------------------------------------------
#create table if not exists db (
#  db_id                    INTEGER          NOT NULL constraint db_pk primary key autoincrement,
#  owner_group_id           INTEGER          NOT NULL DEFAULT 1,                  -- 1 is the 'all-users' group
#  db_env                   VARCHAR(8)       NOT NULL default 'prod',
#  db_name                  VARCHAR(50)      NOT NULL,
#  db_label                 VARCHAR(99)      NULL,
#  db_desc                  VARCHAR(255)     NULL,
#  order_idx                INTEGER          NOT NULL DEFAULT 9999,
#  dbtype                   VARCHAR(99)      NULL,
#  db_connection_id         INTEGER          NULL,
#  schema_name              VARCHAR(99)      NULL,
#  status                   VARCHAR(1)       NOT NULL DEFAULT 'X',    -- [A]ctive, [X]inactive
#  reserved_size            BIGINT           NULL,
#  est_current_size         BIGINT           NULL,
#  last_refresh_dttm        DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
#  user_mode                TINYINT          not null default 7,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  group_mode               TINYINT          not null default 7,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  other_mode               TINYINT          not null default 0,                  -- bit field like unix perms: bit2=read, bit1=write, bit0=exec/admin
#  modify_user_id           INTEGER          NOT NULL DEFAULT 0,
#  create_user_id           INTEGER          NOT NULL DEFAULT 0,
#  modify_dttm              DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
#  create_dttm              DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
#);

